package cn.edu.imut.dao;

import cn.edu.imut.bean.CategoryBean;
import cn.edu.imut.bean.ProductBean;
import cn.edu.imut.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class ProductDao {

    public List<ProductBean> getProductsByCategory(int categoryId) {
        List<ProductBean> products = new ArrayList<>();
        String sql = "SELECT p.*, c.category_name " +
                "FROM gs_products p " +
                "LEFT JOIN gs_categories c ON p.product_category = c.category_id " +
                "WHERE p.product_category = ?";
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, categoryId);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                products.add(new ProductBean(
                        rs.getInt("product_id"),
                        rs.getString("product_name"),
                        rs.getInt("product_category"),
                        rs.getInt("sales"),
                        rs.getDouble("price"),
                        rs.getInt("stock")
                ));
            }
        } catch (Exception e) {
            System.err.println("根据分类获取商品时发生错误：" + e.getMessage());
        }
        return products;
    }



    /**
     * 获取商品列表并按照指定字段排序
     */
    public List<ProductBean> getSortedProducts(String sortBy, int offset, int limit) {
        List<ProductBean> products = new ArrayList<>();
        String column = "price"; // 默认排序字段
        if ("销量".equals(sortBy)) {
            column = "sales";
        }

        String sql = "SELECT p.*, c.category_name " +
                "FROM gs_products p " +
                "LEFT JOIN gs_categories c ON p.product_category = c.category_id " +
                "ORDER BY " + column + " DESC " +
                "LIMIT ?, ?";

        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, offset);
            pstmt.setInt(2, limit);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                products.add(mapResultSetToProduct(rs));
            }
        } catch (Exception e) {
            System.err.println("获取排序商品时发生错误：" + e.getMessage());
        }
        return products;
    }


    /**
     * 根据关键词搜索商品
     */
    public List<ProductBean> searchProducts(String keyword) {
        List<ProductBean> products = new ArrayList<>();
        String sql = "SELECT * FROM gs_products WHERE product_name LIKE ?";

        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, "%" + keyword + "%");
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                products.add(mapResultSetToProduct(rs));
            }
        } catch (Exception e) {
            System.err.println("搜索商品时发生错误：" + e.getMessage());
        }
        return products;
    }


    /**
     * 获取用户收藏的商品列表
     */
    public List<ProductBean> getFavoriteProductsByUsername(String username) {
        List<ProductBean> favoriteProducts = new ArrayList<>();
        String sql = "SELECT p.*, c.category_name " +
                "FROM gs_products p " +
                "JOIN gs_favorites f ON p.product_id = f.product_id " +
                "JOIN gs_users u ON f.user_id = u.user_id " +
                "LEFT JOIN gs_categories c ON p.product_category = c.category_id " +
                "WHERE u.username = ?";

        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                favoriteProducts.add(mapResultSetToProduct(rs));
            }
        } catch (Exception e) {
            System.err.println("获取用户收藏商品时发生错误：" + e.getMessage());
        }
        return favoriteProducts;
    }



    /**
     * 收藏商品
     */
    public boolean addFavorite(int userId, int productId) {
        String sql = "INSERT INTO gs_favorites (user_id, product_id) VALUES (?, ?)";
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            pstmt.setInt(2, productId);
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            System.err.println("收藏商品失败：" + e.getMessage());
        }
        return false;
    }

    /**
     * 检查是否已收藏
     */
    public boolean isProductFavorited(int userId, int productId) {
        String sql = "SELECT * FROM gs_favorites WHERE user_id = ? AND product_id = ?";
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            pstmt.setInt(2, productId);
            ResultSet rs = pstmt.executeQuery();
            return rs.next();
        } catch (Exception e) {
            System.err.println("检查收藏状态失败：" + e.getMessage());
        }
        return false;
    }



    public ProductBean getProductDetails(int productId) {
        String sql = "SELECT p.*, c.category_name " +
                "FROM gs_products p " +
                "LEFT JOIN gs_categories c ON p.product_category = c.category_id " +
                "WHERE p.product_id = ?";
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, productId);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return new ProductBean(
                        rs.getInt("product_id"),
                        rs.getString("product_name"),
                        rs.getInt("product_category"),
                        rs.getInt("sales"),
                        rs.getDouble("price"),
                        rs.getInt("stock")
                );
            }
        } catch (Exception e) {
            System.err.println("获取商品详情失败：" + e.getMessage());
        }
        return null;
    }

    public List<CategoryBean> getAllCategories() {
        List<CategoryBean> categories = new ArrayList<>();
        String sql = "SELECT * FROM gs_categories";

        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                categories.add(new CategoryBean(
                        rs.getInt("category_id"),            // 分类ID
                        rs.getString("category_label"),      // 分类标签
                        rs.getString("category_name"),       // 分类名称
                        rs.getString("category_description") // 分类描述
                ));
            }
        } catch (Exception e) {
            System.err.println("获取所有分类时发生错误：" + e.getMessage());
        }
        return categories;
    }


    public List<ProductBean> getProductsByCategory(String categoryName) {
        List<ProductBean> products = new ArrayList<>();
        String sql = "SELECT p.*, c.category_name " +
                "FROM gs_products p " +
                "JOIN gs_categories c ON p.product_category = c.category_id " +
                "WHERE c.category_name = ?";

        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, categoryName);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                products.add(new ProductBean(
                        rs.getInt("product_id"),
                        rs.getString("product_name"),
                        rs.getInt("product_category"),
                        rs.getInt("sales"),
                        rs.getDouble("price"),
                        rs.getInt("stock")
                ));
            }
        } catch (Exception e) {
            System.err.println("根据分类名获取商品时发生错误：" + e.getMessage());
        }
        return products;
    }


    /**
     * 映射 ResultSet 到 ProductBean
     */
    private ProductBean mapResultSetToProduct(ResultSet rs) throws Exception {
        return new ProductBean(
                rs.getInt("product_id"),
                rs.getString("product_name"),
                rs.getInt("product_category"),
                rs.getInt("sales"),
                rs.getDouble("price"),
                rs.getInt("stock")
        );
    }

    public void addNewProduct() {
        Scanner sc = new Scanner(System.in);

        try {
            System.out.println("请输入商品名称：");
            String productName = sc.nextLine();

            // 校验商品名称是否为空
            if (productName == null || productName.trim().isEmpty()) {
                System.out.println("商品名称不能为空！");
                return;
            }

            System.out.println("请输入分类ID（必须为正整数）：");
            int categoryId = sc.nextInt();
            sc.nextLine(); // 清空缓冲区
            if (categoryId <= 0) {
                System.out.println("分类ID不合法，请输入正整数！");
                return;
            }

            System.out.println("请输入商品价格（不能为负数）：");
            double price = sc.nextDouble();
            sc.nextLine(); // 清空缓冲区
            if (price < 0) {
                System.out.println("商品价格不能为负数！");
                return;
            }

            System.out.println("请输入库存数量（不能为负数）：");
            int stock = sc.nextInt();
            sc.nextLine(); // 清空缓冲区
            if (stock < 0) {
                System.out.println("库存数量不能为负数！");
                return;
            }

            // 创建新的商品对象
            ProductBean newProduct = new ProductBean(0, productName, categoryId, 0, price, stock);

            // 调用 DAO 层保存商品
            final ProductDao productDao = null;
            boolean result = productDao.addProduct(newProduct);

            if (result) {
                System.out.println("商品添加成功！");
            } else {
                System.out.println("商品添加失败，请重试！");
            }
        } catch (Exception e) {
            System.err.println("添加新商品时发生错误：" + e.getMessage());
            e.printStackTrace();
        }
    }

    // 添加商品
    public boolean addProduct(ProductBean product) {
        String sql = "INSERT INTO gs_products (product_name, product_category, price, stock) VALUES (?, ?, ?, ?)";
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, product.getProductName());
            pstmt.setInt(2, product.getCategoryId());
            pstmt.setDouble(3, product.getPrice());
            pstmt.setInt(4, product.getStock());
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            System.err.println("商品上架失败：" + e.getMessage());
        }
        return false;
    }

    // 下架商品
    public boolean removeProduct(int productId) {
        String sql = "DELETE FROM gs_products WHERE product_id = ?";
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, productId);
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            System.err.println("商品下架失败：" + e.getMessage());
        }
        return false;
    }

    // 更新商品信息
    public boolean updateProduct(ProductBean product) {
        String sql = "UPDATE gs_products SET product_name = ?, price = ?, stock = ? WHERE product_id = ?";
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, product.getProductName());
            pstmt.setDouble(2, product.getPrice());
            pstmt.setInt(3, product.getStock());
            pstmt.setInt(4, product.getProductId());
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            System.err.println("商品信息更新失败：" + e.getMessage());
        }
        return false;
    }


    // 查询所有商品库存
    public void displayAllProducts() {
        List<ProductBean> products = getAllProducts();
        if (products.isEmpty()) {
            System.out.println("当前没有商品！");
        } else {
            System.out.println("以下是所有商品的库存信息：");
            products.forEach(product -> {
                System.out.println("商品ID：" + product.getProductId());
                System.out.println("商品名称：" + product.getProductName());
                System.out.println("分类ID：" + product.getCategoryId());
                System.out.println("销量：" + product.getSalesVolume());
                System.out.println("价格：" + product.getPrice());
                System.out.println("库存：" + product.getStock());
                System.out.println("--------------------------------");
            });
        }
    }





    // 设置库存预警阈值
    public void displayLowStockProducts(int threshold) {
        List<ProductBean> lowStockProducts = getProductsBelowThreshold(threshold);
        if (lowStockProducts.isEmpty()) {
            System.out.println("当前没有库存低于阈值的商品！");
        } else {
            System.out.println("以下是库存低于 " + threshold + " 的商品信息：");
            lowStockProducts.forEach(product -> {
                System.out.println("商品ID：" + product.getProductId());
                System.out.println("商品名称：" + product.getProductName());
                System.out.println("分类ID：" + product.getCategoryId());
                System.out.println("销量：" + product.getSalesVolume());
                System.out.println("价格：" + product.getPrice());
                System.out.println("库存：" + product.getStock());
                System.out.println("--------------------------------");
            });
        }
    }
    public List<ProductBean> getAllProducts() {
        List<ProductBean> products = new ArrayList<>();
        // 使用联表查询获取商品信息和分类名称
        String sql = "SELECT p.product_id, p.product_name, p.product_category, p.sales, p.price, p.stock, " +
                "c.category_name " +
                "FROM gs_products p " +
                "LEFT JOIN gs_categories c ON p.product_category = c.category_id";

        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                products.add(new ProductBean(
                        rs.getInt("product_id"),        // 商品ID
                        rs.getString("product_name"),  // 商品名称
                        rs.getInt("product_category"), // 分类ID
                        rs.getInt("sales"),            // 销量
                        rs.getDouble("price"),         // 价格
                        rs.getInt("stock")             // 库存
                ));
            }
        } catch (Exception e) {
            System.err.println("查询所有商品库存时发生错误：" + e.getMessage());
            e.printStackTrace();
        }

        if (products.isEmpty()) {
            System.out.println("商品列表为空！当前数据库中没有任何商品记录。");
        }

        return products;
    }


    public List<ProductBean> getProductsBelowThreshold(final int threshold) {
        List<ProductBean> lowStockProducts = new ArrayList<>();

        // 校验阈值是否合法
        if (threshold <= 0) {
            System.out.println("库存预警阈值必须为正整数！");
            return lowStockProducts;
        }

        // 联表查询获取低于阈值的商品及分类名称
        String sql = "SELECT p.product_id, p.product_name, p.product_category, p.sales, p.price, p.stock, " +
                "c.category_name " +
                "FROM gs_products p " +
                "LEFT JOIN gs_categories c ON p.product_category = c.category_id " +
                "WHERE p.stock < ?";

        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {

            pstmt.setInt(1, threshold);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                lowStockProducts.add(new ProductBean(
                        rs.getInt("product_id"),        // 商品ID
                        rs.getString("product_name"),  // 商品名称
                        rs.getInt("product_category"), // 分类ID
                        rs.getInt("sales"),            // 销量
                        rs.getDouble("price"),         // 价格
                        rs.getInt("stock")             // 库存
                ));
            }
        } catch (Exception e) {
            System.err.println("查询低库存商品时发生错误：" + e.getMessage());
            e.printStackTrace();
        }

        if (lowStockProducts.isEmpty()) {
            System.out.println("没有商品低于设定的库存阈值（" + threshold + "）。");
        }

        return lowStockProducts;
    }


    public boolean isValidCategoryId(int categoryId) {
        String sql = "SELECT COUNT(*) FROM gs_categories WHERE category_id = ?";
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, categoryId);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0; // 如果分类存在，返回 true
            }
        } catch (Exception e) {
            System.err.println("验证分类 ID 时发生错误：" + e.getMessage());
        }
        return false; // 分类不存在
    }

    public boolean addNewProduct(String name, int categoryId, double price, int stock) {
        String sql = "INSERT INTO gs_products (product_name, product_category, price, stock) VALUES (?, ?, ?, ?)";

        try (Connection connection = DBUtil.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setInt(2, categoryId);
            pstmt.setDouble(3, price);
            pstmt.setInt(4, stock);

            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0; // 如果插入成功，返回 true
        } catch (Exception e) {
            System.err.println("添加新商品时发生错误：" + e.getMessage());
        }
        return false; // 如果插入失败，返回 false
    }
    /**
     * 通过商品id获取商品对象
     */
    public ProductBean getProductById(int productId) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM gs_products WHERE product_id =?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, productId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return new ProductBean(rs.getInt("product_id"),
                        rs.getString("product_name"),
                        rs.getInt("product_category"),
                        rs.getInt("sales"),
                        rs.getDouble("price"),
                        rs.getInt("stock"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,pstmt,rs);
            try {
                if (pstmt!= null) {
                    pstmt.close();
                }
                if (rs!= null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public void updateStock(final int orderProductId, final int i) {
    }
}
